﻿using Dapper;
using Microsoft.Data.SqlClient;
using Model;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace DAL
{
    public class WhInboundDal
    {
        private readonly AppDbContext _dbContext;
        private string URL;
        public WhInboundDal(AppDbContext dbContext)
        {
            dynamic jsonObject = JsonConvert.DeserializeObject(File.ReadAllText("appsettings.json"));
            URL = jsonObject.ConnectionStrings.SqlServer;
            _dbContext = dbContext;
        }

        /// <summary>
        /// 查询全部入库单信息
        /// </summary>
        /// <returns></returns>
        public async Task<List<WhInbound>> GetAllInboundInfo()
        {
            string sql = @"SELECT 
                                wi.stock_order_id,
                                wi.stock_order_no, 
                                wi.stock_type, 
                                wi.order_status, 
                                wi.stock_date, 
                                bw.warehouse_name,
                                up_order_no,
                                COALESCE(STRING_AGG(bp.goods_name, ',') WITHIN GROUP (ORDER BY wid.stock_order_id), '') AS all_goods_name,
                                u2.users_name as operator_by,
                                COALESCE(SUM(wid.current_stock_in_quantity), 0) AS all_order_quantity,
                                COALESCE(SUM(wid.current_stock_in_quantity * wid.purchase_unit_price),0) AS purchase_unit_price_count,
                                u1.users_name AS create_by,
                                sog.org_name AS create_org_name,
                                wi.create_time,
                                u3.users_name AS auditor_by,
                                wi.auditor_time
                            FROM 
                                wh_inbound wi
                                JOIN bi_warehouse bw ON wi.warehouse_id = bw.warehouse_id
                                LEFT JOIN om_purchase_order opo ON wi.source_order_no = opo.up_order_no
                                LEFT JOIN wh_inbound_detail wid ON wi.stock_order_id = wid.stock_order_id
                                LEFT JOIN bi_product bp ON wid.goods_code = bp.goods_code
                                JOIN sys_users u1 ON wi.create_by = u1.users_id
                                LEFT JOIN sys_users u2 ON wi.operator_by = u2.users_id
                                LEFT JOIN sys_users u3 ON wi.auditor_by = u3.users_id
                                LEFT JOIN sys_organization sog ON u1.org_id = sog.org_id
                            WHERE 
                                wi.is_delete = 0
                            GROUP BY 
                                wi.stock_order_id,
                                wi.stock_order_no, 
                                wi.stock_type, 
                                wi.order_status, 
                                wi.stock_date, 
                                bw.warehouse_name, 
                                opo.up_order_no,
                                u1.users_name, 
                                sog.org_name, 
                                wi.create_time, 
                                u3.users_name, 
                                u2.users_name,
                                wi.auditor_time,
                                wi.update_time
                            order by wi.update_time desc";
            using (SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<WhInbound>(sql)).ToList();
            }
        }

        /// <summary>
        /// 查询仓库信息用于选择
        /// </summary>
        /// <returns></returns>
        public async Task<List<WareHouseDTO>> GetWareHouseInfoForSelect()
        {
            string sql = "select warehouse_id,warehouse_name from bi_warehouse";
            using(SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<WareHouseDTO>(sql)).ToList();
            }
        }

        /// <summary>
        /// 多条件查询入库单
        /// </summary>
        /// <param name="wisws"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public async Task<List<WhInbound>> GetSomeInboundInfo(WhInbSelectWithSth wisws)
        {
            // 1. 基础SQL（查询入库单主表及关联表，与原SQL保持一致但修正表关联）
            var sqlHead = @"
        SELECT 
            wi.stock_order_id,
            wi.stock_order_no, 
            wi.stock_type, 
            wi.order_status, 
            wi.stock_date, 
            bw.warehouse_name,
            up_order_no,
            COALESCE(STRING_AGG(bp.goods_name, ',') WITHIN GROUP (ORDER BY wid.stock_order_id), '') AS all_goods_name,
            u2.users_name as operator_by,
            COALESCE(SUM(wid.current_stock_in_quantity), 0) AS all_order_quantity,
            COALESCE(SUM(wid.current_stock_in_quantity * wid.purchase_unit_price),0) AS purchase_unit_price_count,
            u1.users_name AS create_by,
            sog.org_name AS create_org_name,
            wi.create_time,
            u3.users_name AS auditor_by,
            wi.auditor_time
        FROM 
            wh_inbound wi
            JOIN bi_warehouse bw ON wi.warehouse_id = bw.warehouse_id
            LEFT JOIN om_purchase_order opo ON wi.source_order_no = opo.up_order_no
            LEFT JOIN wh_inbound_detail wid ON wi.stock_order_id = wid.stock_order_id
            LEFT JOIN bi_product bp ON wid.goods_code = bp.goods_code
            JOIN sys_users u1 ON wi.create_by = u1.users_id
            LEFT JOIN sys_users u2 ON wi.operator_by = u2.users_id
            LEFT JOIN sys_users u3 ON wi.auditor_by = u3.users_id
            LEFT JOIN sys_organization sog ON u1.org_id = sog.org_id
        WHERE 
            wi.is_delete = 0";

    var sqlTail = @"
                    GROUP BY 
                        wi.stock_order_id,
                        wi.stock_order_no, 
                        wi.stock_type, 
                        wi.order_status, 
                        wi.stock_date, 
                        bw.warehouse_name, 
                        opo.up_order_no,
                        u1.users_name, 
                        sog.org_name, 
                        wi.create_time, 
                        u3.users_name, 
                        u2.users_name,
                        wi.auditor_time,
                        wi.update_time
                    order by wi.update_time desc";

            // 2. 构建动态条件（基于 WhInbSelectWithSth 实体的字段）
            var conditions = new List<string>();
            var parameters = new DynamicParameters();

            // 入库单号（模糊查询）
            if (!string.IsNullOrEmpty(wisws.StockOrderNo))
            {
                conditions.Add("wi.stock_order_no LIKE @StockOrderNo");
                parameters.Add("@StockOrderNo", $"%{wisws.StockOrderNo}%", DbType.String);
            }

            // 入库类型（精确匹配）
            if (!string.IsNullOrEmpty(wisws.StockType))
            {
                conditions.Add("wi.stock_type = @StockType");
                parameters.Add("@StockType", wisws.StockType, DbType.String);
            }

            // 单据状态（精确匹配）
            if (!string.IsNullOrEmpty(wisws.OrderStatus))
            {
                conditions.Add("wi.order_status = @OrderStatus");
                parameters.Add("@OrderStatus", wisws.OrderStatus, DbType.String);
            }

            // 仓库ID（精确匹配，关联仓库表）
            if (wisws.WarehouseId > 0)
            {
                conditions.Add("wi.warehouse_id = @WarehouseId");  // 用入库单的仓库ID关联
                parameters.Add("@WarehouseId", wisws.WarehouseId, DbType.Int64);
            }

            // 3. 拼接最终SQL（条件不为空时用AND连接）
            var finalSql = sqlHead;
            if (conditions.Count > 0)
            {
                finalSql += " AND " + string.Join(" AND ", conditions);  // 注意：原SQL的WHERE后已有条件，这里用AND拼接
            }
            finalSql += sqlTail;

            // 4. 执行参数化查询（确保返回类型与方法声明一致）
            using (var con = new SqlConnection(URL))  // URL应为配置文件中的数据库连接字符串
            {
                try
                {
                    await con.OpenAsync();
                    // 执行查询并映射到 WhInbound 实体（确保实体字段与查询结果列名匹配）
                    var result = await con.QueryAsync<WhInbound>(finalSql, parameters);
                    return result.AsList();  // 转换为List<WhInbound>返回
                }
                catch (SqlException ex)
                {
                    // 记录SQL异常（包含参数信息，但避免暴露原始SQL）
                    Console.WriteLine($"入库单查询SQL异常：{ex.Message}，参数：{JsonConvert.SerializeObject(parameters)}");
                    throw new Exception("查询入库单失败，请联系管理员", ex);  // 包装异常，隐藏敏感信息
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"入库单查询异常：{ex.Message}");
                    throw;  // 抛出其他异常（如类型转换失败）
                }
            }
        }

        /// <summary>
        /// 入库单撤销(可批量)
        /// </summary>
        /// <param name="stockOrderId"></param>
        /// <param name="orderStatu"></param>
        /// <returns></returns>
        public async Task<int> ChangeWhInboundStatu(List<long> orderIdList, string orderStatu, long userId)
        {
            // 构建批量更新的SQL（使用表值参数或循环执行，此处采用循环执行单条更新）
            string sql = @"update wh_inbound set 
                    order_status = @OrderStatus,
                    update_by = @UpdateBy,
                    update_time = getdate()
                    where stock_order_id = @StockOrderId";

            int totalAffected = 0;
            using (var con = new SqlConnection(URL))
            {
                await con.OpenAsync(); // 显式打开连接，提升批量操作性能
                foreach (var orderId in orderIdList)
                {
                    // 逐条执行更新，累计受影响行数
                    totalAffected += await con.ExecuteAsync(
                        sql: sql,
                        param: new
                        {
                            StockOrderId = orderId,       // 使用循环中的当前订单ID
                            OrderStatus = orderStatu,     // 目标状态
                            UpdateBy = userId             // 更新人ID（修正参数名错误）
                        }
                    );
                }
            }
            return totalAffected; // 返回总受影响行数
        }

        /// <summary>
        /// 入库单删除(可批量)
        /// </summary>
        /// <param name="orderIdList"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentException"></exception>
        /// <exception cref="InvalidOperationException"></exception>
        public async Task<int> DeleteWhInbound(List<long> orderIdList, long userId)
        {
            if (orderIdList == null || !orderIdList.Any())
            {
                throw new ArgumentException("订单ID列表不能为空", nameof(orderIdList));
            }
            var validOrderIds = orderIdList.Where(id => id > 0).ToList();
            if (!validOrderIds.Any())
            {
                throw new ArgumentException("订单ID必须为正整数", nameof(orderIdList));
            }
            if (userId <= 0)
            {
                throw new ArgumentException("操作人ID无效", nameof(userId));
            }

            using (SqlConnection con = new SqlConnection(URL))
            {
                await con.OpenAsync();
                using (var transaction = con.BeginTransaction())
                {
                    try
                    {
                        const string checkSql = @"
                    SELECT stock_order_id 
                    FROM wh_inbound
                    WHERE stock_order_id IN @OrderIds 
                    AND is_delete = '0'
                    AND order_status IN ('0', '1')";

                        var deletableOrderIds = await con.QueryAsync<long>(
                            checkSql,
                            new { OrderIds = validOrderIds },
                            transaction
                        );

                        if (!deletableOrderIds.Any())
                        {
                            transaction.Commit();
                            return 0;
                        }
                        const string deleteDetailSql = @"
                    UPDATE wh_inbound_detail
                    SET is_delete = '1',
                        update_by = @UserId,
                        update_time = GETDATE()
                    WHERE stock_order_id IN @OrderIds 
                    AND is_delete = '0'";

                        await con.ExecuteAsync(
                            deleteDetailSql,
                            new { OrderIds = deletableOrderIds, UserId = userId },
                            transaction
                        );
                        const string deleteMainSql = @"
                    UPDATE wh_inbound
                    SET is_delete = '1',
                        update_time = GETDATE(),
                        update_by = @UserId 
                    WHERE stock_order_id IN @OrderIds 
                    AND is_delete = '0'";

                        var mainAffected = await con.ExecuteAsync(
                            deleteMainSql,
                            new { OrderIds = deletableOrderIds, UserId = userId },
                            transaction
                        );

                        transaction.Commit();
                        return mainAffected;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new InvalidOperationException("删除失败，请稍后重试", ex);
                    }
                }
            }
        }

        /// <summary>
        /// 入库单审核/驳回
        /// </summary>
        /// <param name="whInOrderId"></param>
        /// <param name="orderStatu"></param>
        /// <param name="auditorId"></param>
        /// <returns></returns>
        public async Task<int> AuditeWhInboundOrder(
    List<long> whOrderIdList,
    string orderStatu,
    long auditorId,
    string auditorComment
)
        {
            string updateSql = @"
        UPDATE wh_inbound 
        SET 
            order_status = @OrderStatu,
            auditor_by = @AuditorId,
            update_by = @AuditorId,
            auditor_time = GETDATE(),
            update_time = GETDATE(),
            auditor_comment = ISNULL(@AuditorComment, '')
        WHERE 
            stock_order_id = @WhInOrderId";

            // 存储过程调用语句（保持不变）
            string executeSpSql = "EXEC sp_HandleInboundAudit @stock_order_id = @WhInOrderId";

            int totalAffected = 0;

            using (SqlConnection connection = new SqlConnection(URL))
            {
                await connection.OpenAsync();
                SqlTransaction transaction = null;

                try
                {
                    transaction = (SqlTransaction)await connection.BeginTransactionAsync(IsolationLevel.ReadCommitted);
                    int totalUpdateRows = 0;
                    int totalSpRows = 0;

                    foreach (long orderId in whOrderIdList)
                    {
                        // 1. 更新入库单状态（用 ExecuteAsync 正确获取影响行数）
                        int updateRows = await connection.ExecuteAsync(
                            sql: updateSql,
                            param: new
                            {
                                WhInOrderId = orderId,
                                OrderStatu = orderStatu,
                                AuditorId = auditorId,
                                AuditorComment = auditorComment
                            },
                            transaction: transaction
                        );
                        totalUpdateRows += updateRows;

                        // 2. 执行存储过程（用 QueryFirstOrDefaultAsync 获取返回的行数）
                        // 存储过程成功时返回 AffectedRows（int），失败时返回 ErrorMsg（string），需区分处理
                        var spResult = await connection.QueryFirstOrDefaultAsync<dynamic>(
                            sql: executeSpSql,
                            param: new { WhInOrderId = orderId },
                            transaction: transaction
                        );

                        if (spResult != null)
                        {
                            // 判断返回的是行数还是错误信息（根据存储过程的返回字段名）
                            if (spResult.AffectedRows != null)
                            {
                                totalSpRows += (int)spResult.AffectedRows; // 累加存储过程影响的行数
                            }
                            else if (spResult.ErrorMsg != null)
                            {
                                // 存储过程执行失败，抛出错误
                                throw new InvalidOperationException($"存储过程执行失败：{spResult.ErrorMsg}");
                            }
                        }
                    }

                    // 总影响行数 = 更新语句行数 + 存储过程行数
                    totalAffected = totalUpdateRows + totalSpRows;
                    Console.WriteLine($"更新语句影响行数：{totalUpdateRows}");
                    Console.WriteLine($"存储过程影响行数：{totalSpRows}");

                    await transaction.CommitAsync();
                }
                catch (Exception ex)
                {
                    if (transaction != null)
                        await transaction.RollbackAsync();
                    throw new InvalidOperationException($"入库单审核失败：{ex.Message}", ex);
                }
                finally
                {
                    transaction?.Dispose();
                }
            }

            return totalAffected;
        }

        /// <summary>
        /// 关联单号查询
        /// </summary>
        /// <returns></returns>
        public async Task<List<OmPurchaseOrderSourceDetail>> OmPurchaseOrderSourceDetailsForSelect()
        {
            string sql = @"SELECT 
                                opo.up_order_id, 
                                opo.up_order_no, 
                                opo.order_status,
                                opo.buyer_date,  
                                bs.supplier_id,
                                bs.supplier_name,
                                sog.org_name as buyer_org_name,
                                su.users_name,
                                bs.contact_person,
                                bs.contact_phone, 
                                SUM(opod.order_quantity) AS order_quantity, -- 原有：进货单总数量（不变）
                                SUM(opod.received_quantity) AS received_quantity, -- 原有：已入库数量（不变）
                                SUM(opod.order_quantity) - SUM(opod.received_quantity) AS unstocked_quantity,
                                SUM(opod.purchase_unit_price * opod.order_quantity) AS purchase_unit_price -- 原有：进货总金额（不变）
                            FROM om_purchase_order opo
                            INNER JOIN bi_supplier bs ON opo.supplier_id = bs.supplier_id
                            INNER JOIN sys_users su ON opo.buyer_by = su.users_id
                            INNER JOIN sys_organization sog ON su.org_id = sog.org_id
                            INNER JOIN om_purchase_order_detail opod ON opo.up_order_id = opod.up_order_id
                            INNER JOIN bi_product bp ON opod.goods_code = bp.goods_code
                            WHERE opo.is_delete = 0 
                              AND opo.order_status IN (3, 4)
                            GROUP BY 
                                opo.up_order_id, 
                                opo.up_order_no, 
                                opo.order_status,
                                opo.buyer_date,  
                                bs.supplier_id,
                                bs.supplier_name,
                                sog.org_name,
                                su.users_name,
                                bs.contact_person,
                                bs.contact_phone
                            ORDER BY opo.up_order_id;
                        ";
            using(SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<OmPurchaseOrderSourceDetail>(sql)).ToList();
            }
        }

        /// <summary>
        /// 多条件查询关联订单
        /// </summary>
        /// <param name="wisws"></param>
        /// <returns></returns>
        public async Task<List<OmPurchaseOrderSourceDetail>> GetOmPurchaseOrderSourceWithSth(WhInSelectWithSth wisws)
        {
            // 基础 SQL（移除原固定条件 opo.up_order_id IN @OrderIds）
            var sql = @"SELECT 
                    opo.up_order_id, 
                    opo.up_order_no, 
                    opo.order_status,
                    opo.buyer_date,  
                    bs.supplier_id,
                    bs.supplier_name,
                    sog.org_name as buyer_org_name,
                    su.users_name,
                    bs.contact_person,
                    bs.contact_phone, 
                    SUM(opod.order_quantity) AS order_quantity,
                    SUM(opod.received_quantity) AS received_quantity,
                    SUM(opod.order_quantity) - SUM(opod.received_quantity) AS unstocked_quantity,
                    SUM(opod.purchase_unit_price * opod.order_quantity) AS purchase_unit_price
                FROM om_purchase_order opo
                INNER JOIN bi_supplier bs ON opo.supplier_id = bs.supplier_id
                INNER JOIN sys_users su ON opo.buyer_by = su.users_id
                INNER JOIN sys_organization sog ON su.org_id = sog.org_id
                INNER JOIN om_purchase_order_detail opod ON opo.up_order_id = opod.up_order_id
                INNER JOIN bi_product bp ON opod.goods_code = bp.goods_code
                WHERE opo.is_delete = 0 
                  AND opo.order_status IN (3, 4)";

            // 动态条件列表与参数
            var conditions = new List<string>();
            var parameters = new DynamicParameters();

            // 1. 进货单号（模糊查询）
            if (!string.IsNullOrEmpty(wisws.UpOrderNo))
            {
                conditions.Add("opo.up_order_no LIKE @UpOrderNo");
                parameters.Add("@UpOrderNo", $"%{wisws.UpOrderNo}%"); // 前后加百分号实现模糊匹配
            }

            // 2. 单据状态（精确匹配，允许传入多个状态）
            if (!string.IsNullOrEmpty(wisws.OrderStatus))
            {
                // 假设 OrderStatus 格式为逗号分隔的字符串（如 "3,4"）
                var statusList = wisws.OrderStatus.Split(',', StringSplitOptions.RemoveEmptyEntries)
                                                 .Select(s => s.Trim())
                                                 .ToList();
                if (statusList.Any())
                {
                    conditions.Add("opo.order_status IN @OrderStatusList");
                    parameters.Add("@OrderStatusList", statusList);
                }
            }

            // 3. 供应商ID（精确匹配）
            if (wisws.SupplierId > 0) // 假设 ID 为正数，0 表示未传
            {
                conditions.Add("opo.supplier_id = @SupplierId");
                parameters.Add("@SupplierId", wisws.SupplierId);
            }

            // 4. 进货人ID（精确匹配）
            if (wisws.BuyerId > 0)
            {
                conditions.Add("opo.buyer_by = @BuyerId");
                parameters.Add("@BuyerId", wisws.BuyerId);
            }

            // 5. 进货部门ID（精确匹配，通过用户表关联）
            if (wisws.BuyerOrgId > 0)
            {
                conditions.Add("su.org_id = @BuyerOrgId");
                parameters.Add("@BuyerOrgId", wisws.BuyerOrgId);
            }

            // 6. 进货日期（精确匹配某天，若需范围可扩展为 >= 和 <=）
            if (wisws.OmPurDate != DateTime.MinValue)
            {
                // 匹配日期部分（忽略时间）
                conditions.Add("CONVERT(date, opo.buyer_date) = CONVERT(date, @OmPurDate)");
                parameters.Add("@OmPurDate", wisws.OmPurDate);
            }

            // 拼接动态条件
            if (conditions.Any())
            {
                sql += " AND " + string.Join(" AND ", conditions);
            }

            // 分组、排序
            sql += @" GROUP BY 
                opo.up_order_id, 
                opo.up_order_no, 
                opo.order_status,
                opo.buyer_date,  
                bs.supplier_id,
                bs.supplier_name,
                sog.org_name,
                su.users_name,
                bs.contact_person,
                bs.contact_phone
            ORDER BY opo.up_order_id;";

            // 执行查询
            using (var con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<OmPurchaseOrderSourceDetail>(sql, parameters)).ToList();
            }
        }

        /// <summary>
        /// 通过进货订单ID查询商品详细信息
        /// </summary>
        /// <param name="upOrderId"></param>
        /// <returns></returns>
        public async Task<List<OmPurOrderGoodsDetail>> GetOmPurOrderGoodsDetail(long upOrderId)
        {
            string sql = @"select opo.up_order_id,
                                  opod.goods_code,
                                  bp.goods_name,
                                  bp.specification,
                                  bpc.goods_type_name,
                                  opod.purchase_unit_price,
                                  opod.order_quantity,
                                  opod.purchase_unit_price*opod.order_quantity as purchase_unit_count,
                                  opod.received_quantity,
                                  opod.order_quantity - opod.received_quantity as non_purchase_quantity,
                                  opo.buyer_date,
                                  bs.supplier_name
                           from om_purchase_order opo
                           inner join om_purchase_order_detail opod on opo.up_order_id = opod.up_order_id
                           inner join bi_product bp on opod.goods_code = bp.goods_code
                           inner join bi_product_category bpc on bp.goods_type_id = bpc.goods_type_id
                           inner join bi_supplier bs on opo.supplier_id = bs.supplier_id
                           where opo.is_delete = 0 and opo.up_order_id = @UpOrderId
                           and opod.received_quantity<opod.order_quantity";
            using(SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<OmPurOrderGoodsDetail>(sql, new { UpOrderId = upOrderId })).ToList();
            }
        }

        /// <summary>
        /// 根据仓库ID查询库位信息和库存量用于入库选择
        /// </summary>
        /// <param name="warehouseId"></param>
        /// <returns></returns>
        public async Task<List<WhLocationSelect>> SelectWhLocationForInbound(long warehouseId)
        {
            string sql = @"SELECT 
                                bw.warehouse_id,
                                bw.warehouse_name,
                                wl.storage_location_id,
                                wl.storage_location_name,
                                wl.storage_location_capacity,
                                -- 用ISNULL处理空值：无库存时显示0
                                ISNULL(SUM(winv.available_inventory_qty), 0) AS total_available_inventory
                            FROM 
                                bi_warehouse bw
                            INNER JOIN 
                                wh_location wl ON bw.warehouse_id = wl.warehouse_id
                            LEFT JOIN 
                                wh_inventory winv ON wl.storage_location_id = winv.storage_location_id
                            WHERE 
                                bw.warehouse_id = @WarehouseId 
                                AND bw.is_delete = 0
                            GROUP BY 
                                bw.warehouse_id,
                                bw.warehouse_name,
                                wl.storage_location_id,
                                wl.storage_location_name,
                                wl.storage_location_capacity;";
            using(SqlConnection con = new SqlConnection(URL))
            {
                return (await con.QueryAsync<WhLocationSelect>(sql, new { WarehouseId = warehouseId })).ToList();
            }
        }

        /// <summary>
        /// 添加入库单、入库明细和附件
        /// </summary>
        /// <param name="wifa"></param>
        /// <param name="widfaList"></param>
        /// <param name="sattList"></param>
        /// <returns></returns>
        public int AddWhInbound(WhInboundForAdd wifa,
                       List<WhInboundDetailForAdd> widfaList,
                       List<SysAttachmentDTO> sattList = null)
        {
            int result = 0; // 1=成功，0=失败
            long inboundId = 0; // 入库单主表自增ID（用于关联明细和附件）

            // 外层using确保连接自动释放
            using (SqlConnection con = new SqlConnection(URL))
            {
                con.Open();
                // 开启事务，确保主表、明细表、附件表操作要么全成功，要么全失败
                using (SqlTransaction tran = con.BeginTransaction())
                {
                    try
                    {
                        #region 1. 插入入库单主表（wh_inbound）
                        string mainSql = @"
                                            INSERT INTO wh_inbound(
                                                warehouse_id,
                                                stock_order_no,
                                                source_order_no,
                                                stock_date,
                                                stock_type,
                                                order_status,
                                                operator_by,
                                                operator_date,
                                                is_delete,
                                                create_by,
                                                create_time,
                                                update_by,
                                                update_time
                                            )
                                            VALUES(
                                                @WarehouseId,
                                                @StockOrderNo,
                                                @SourceOrderNo,
                                                @StockDate,
                                                @StockType,
                                                @OrderStatus,
                                                @OperatorBy,
                                                @OperatorDate,
                                                @IsDelete,
                                                @CreateBy,
                                                @CreateTime,
                                                @UpdateBy,
                                                @UpdateTime
                                            );
                                            SELECT SCOPE_IDENTITY();"; // 获取刚插入的自增ID

                        using (SqlCommand mainCmd = new SqlCommand(mainSql, con, tran))
                        {
                            // 主表参数赋值（与实体类字段对应）
                            mainCmd.Parameters.AddWithValue("@WarehouseId", wifa.WareHouseId);
                            mainCmd.Parameters.AddWithValue("@StockOrderNo", wifa.StockOrderNo);
                            mainCmd.Parameters.AddWithValue("@SourceOrderNo",
                                string.IsNullOrEmpty(wifa.SourceOrderNo) ? DBNull.Value : (object)wifa.SourceOrderNo);
                            mainCmd.Parameters.AddWithValue("@StockDate", wifa.StockDate);
                            mainCmd.Parameters.AddWithValue("@StockType", wifa.StockType);
                            mainCmd.Parameters.AddWithValue("@OrderStatus",1);
                            mainCmd.Parameters.AddWithValue("@OperatorBy", wifa.OperatorBy);
                            mainCmd.Parameters.AddWithValue("@OperatorDate", wifa.OperatorDate);
                            mainCmd.Parameters.AddWithValue("@IsDelete", 0); // 0=未删除（逻辑删除）
                            mainCmd.Parameters.AddWithValue("@CreateBy", wifa.OperatorBy); // 创建人=经办人
                            mainCmd.Parameters.AddWithValue("@CreateTime", DateTime.Now); // 创建时间=当前时间
                            mainCmd.Parameters.AddWithValue("@UpdateBy", wifa.OperatorBy); // 首次修改人=经办人
                            mainCmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now); // 首次修改时间=当前时间

                            // 执行并获取主表ID（用于关联明细和附件）
                            inboundId = Convert.ToInt64(mainCmd.ExecuteScalar());
                            if (inboundId <= 0)
                            {
                                throw new Exception("入库单主表插入失败，未生成有效ID");
                            }
                        }
                        #endregion

                        #region 2. 插入入库单明细表（wh_inbound_detail）
                        if (widfaList != null && widfaList.Count > 0)
                        {
                            // 调整SQL字段，确保与VALUES参数数量一致
                            string detailSql = @"
        INSERT INTO wh_inbound_detail(
            stock_order_id,        -- 1. 关联主表ID
            goods_code,            -- 2. 商品编号
            storage_location_id,   -- 3. 库位ID
            current_stock_in_quantity, -- 4. 本次入库数量
            order_quantity,        -- 5. 进货数量
            product_unit,          -- 6. 货品单位
            purchase_unit_price,   -- 7. 进货单价
            batch_no,              -- 8. 批次号
            purchase_amount,       -- 9. 进货金额
            is_delete,             -- 10. 逻辑删除标识
            create_by,             -- 11. 创建人（经办人ID）
            create_time,           -- 12. 创建时间
            update_by,             -- 13. 修改人（同创建人）
            update_time,           -- 14. 修改时间（同创建时间）
            attachment_code,       -- 15. 附件Code
            remarks                -- 16. 备注
        )
        VALUES(
            @InboundId,            -- 1. 对应stock_order_id
            @GoodsCode,            -- 2. 对应goods_code
            @StorageLocationId,    -- 3. 对应storage_location_id
            @CurrentStockInQuantity, -- 4. 对应current_stock_in_quantity
            @OrderQuantity,        -- 5. 对应order_quantity
            @ProductUnit,          -- 6. 对应product_unit
            @PurchaseUnitPrice,    -- 7. 对应purchase_unit_price
            @BatchNo,              -- 8. 对应batch_no
            @PurchaseAmount,       -- 9. 对应purchase_amount
            @IsDelete,             -- 10. 对应is_delete
            @CreateBy,             -- 11. 对应create_by
            @CreateTime,           -- 12. 对应create_time
            @UpdateBy,             -- 13. 对应update_by
            @UpdateTime,           -- 14. 对应update_time
            @AttachmentCode,       -- 15. 对应attachment_code
            @Remarks               -- 16. 对应remarks
        );";

                            foreach (var detail in widfaList)
                            {
                                using (SqlCommand detailCmd = new SqlCommand(detailSql, con, tran))
                                {
                                    // 1. 关联主表ID（入库单主表自增ID）
                                    detailCmd.Parameters.AddWithValue("@InboundId", inboundId);
                                    // 2. 商品编号（从明细实体获取）
                                    detailCmd.Parameters.AddWithValue("@GoodsCode", string.IsNullOrEmpty(detail.GoodsCode) ? DBNull.Value : (object)detail.GoodsCode);
                                    // 3. 库位ID（处理0的情况，0视为未选择）
                                    detailCmd.Parameters.AddWithValue("@StorageLocationId", detail.StorageLocationId <= 0 ? DBNull.Value : (object)detail.StorageLocationId);
                                    // 4. 本次入库数量（实体字段：CurrentStockInQuantity）
                                    detailCmd.Parameters.AddWithValue("@CurrentStockInQuantity", detail.CurrentStockInQuantity);
                                    // 5. 进货数量（从明细实体获取）
                                    detailCmd.Parameters.AddWithValue("@OrderQuantity", detail.OrderQuantity);
                                    // 6. 货品单位（默认“吨”，可从实体获取）
                                    detailCmd.Parameters.AddWithValue("@ProductUnit", string.IsNullOrEmpty(detail.ProductUnit) ? "吨" : detail.ProductUnit);
                                    // 7. 进货单价（从明细实体获取，处理null/0）
                                    detailCmd.Parameters.AddWithValue("@PurchaseUnitPrice", detail.PurchaseUnitPrice);
                                    // 8. 批次号（空值处理）
                                    detailCmd.Parameters.AddWithValue("@BatchNo", string.IsNullOrEmpty(detail.BatchNo) ? DBNull.Value : (object)detail.BatchNo);
                                    // 9. 进货金额（从明细实体获取，处理null/0）
                                    detailCmd.Parameters.AddWithValue("@PurchaseAmount", detail.PurchaseAmount);
                                    // 10. 逻辑删除标识（默认0=未删除）
                                    detailCmd.Parameters.AddWithValue("@IsDelete", 0);
                                    // 11. 创建人（复用主表的经办人ID，即wifa.OperatorBy）
                                    detailCmd.Parameters.AddWithValue("@CreateBy", wifa.OperatorBy);
                                    // 12. 创建时间（当前时间）
                                    detailCmd.Parameters.AddWithValue("@CreateTime", DateTime.Now);
                                    // 13. 修改人（首次创建=创建人）
                                    detailCmd.Parameters.AddWithValue("@UpdateBy", wifa.OperatorBy);
                                    // 14. 修改时间（首次创建=当前时间）
                                    detailCmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
                                    // 15. 附件Code（空值处理）
                                    detailCmd.Parameters.AddWithValue("@AttachmentCode", (object)detail.AttachmentCode ?? DBNull.Value);
                                    // 16. 备注（空值替换为“无”）
                                    detailCmd.Parameters.AddWithValue(
                                        "@Remarks",
                                        string.IsNullOrEmpty(detail.Remarks) ? (object)"无" : detail.Remarks
                                    );

                                    detailCmd.ExecuteNonQuery(); // 执行插入
                                }
                            }
                        }
                        #endregion

                        #region 3. 插入附件表（sys_attachment）
                        if (sattList != null && sattList.Count > 0)
                        {
                            string attachmentSql = @"
                                                    INSERT INTO sys_attachment(
                                                        attachment_code,
                                                        attachment_address,
                                                        create_by,  
                                                        create_time,
                                                        update_by,  
                                                        update_time
                                                            )
                                                    VALUES(
                                                        @AttachmentCode,
                                                        @AttachmentAddress,
                                                        @CreateBy,
                                                        @CreateTime,
                                                        @UpdateBy,
                                                        @UpdateTime
                                                    ); ";
        
                            foreach (var attachment in sattList)
                            {
                                using (SqlCommand attachCmd = new SqlCommand(attachmentSql, con, tran))
                                {
                                    // 附件表参数赋值（关联入库单ID）
                                    attachCmd.Parameters.AddWithValue("@AttachmentCode", attachment.AttachmentCode);
                                    attachCmd.Parameters.AddWithValue("@AttachmentAddress", attachment.AttachmentAddress);
                                    attachCmd.Parameters.AddWithValue("@CreateBy", attachment.CreateBy);
                                    attachCmd.Parameters.AddWithValue("@CreateTime", DateTime.Now);
                                    attachCmd.Parameters.AddWithValue("@UpdateBy", attachment.UpdateBy ?? attachment.CreateBy);
                                    attachCmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now);

                                    attachCmd.ExecuteNonQuery(); // 执行插入
                                }
                            }
                        }
                        #endregion

                        // 所有操作成功，提交事务
                        tran.Commit();
                        result = 1; // 标记成功
                    }
                    catch (Exception ex)
                    {
                        // 发生异常，回滚事务
                        tran.Rollback();
                        // 记录异常日志（实际项目中建议使用日志框架）
                        Console.WriteLine($"插入入库单失败：{ex.Message}");
                        result = 0; // 标记失败
                        throw;
                    }
                }
            }
            return result;
        }
    }
}
